package saleMarket.dao;

import saleMarket.domain.Logistics;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.TreeSet;

/**
 * @author 张瑞祥
 * @version 1.0  12-07-2020
 */

public class LogisticsDao {
    /**
     *单例模式
     *该类负责创建自己的对象，同时确保只有单个对象被创建。
     * 这个类提供了一种访问其唯一的对象的方式，可以直接访问，不需要实例化该类的对象。
     */
    private static LogisticsDao logisticsDao = new LogisticsDao();
    private LogisticsDao(){}

    public static LogisticsDao getInstance(){ return logisticsDao; }
    /**
     * @param conn      连接
     * @param conn 来自Service的Connection对象
     * @return Logistics对象
     * @throws SQLException
     */
    //查询单个顾客方法
    public Logistics find(Integer id, Connection conn) throws SQLException {
        Logistics logistics = null;
        //编辑预编译语句
        PreparedStatement find_pstmt = conn.prepareStatement("SELECT * FROM logistics WHERE id = ?");
        //设置预编译参数
        find_pstmt.setInt(1,id);
        //执行查询并获取查询结果
        ResultSet find_resultSet = find_pstmt.executeQuery();
        //赋值
        if (find_resultSet.next()){
            String no = find_resultSet.getString("no");
            String name = find_resultSet.getString("name");
            Integer phoneNumber = find_resultSet.getInt("phoneNumber");
            String description = find_resultSet.getString("description");
            //创建相应的顾客对象
            logistics = new Logistics(id,no,name,phoneNumber,description);
        }
        //释放资源
        JdbcHelper.close(find_resultSet,find_pstmt);
        //返回查询结果
        return logistics;
    }
    /**
     * @param conn       连接
     * @param conn 来自Service的Connection对象
     * @return collection<Logistics></>
     * @throws SQLException
     */
    //查询所有顾客方法
    public Collection<Logistics> findAll(Connection conn) throws SQLException {
        Collection<Logistics> logisticses = new ArrayList<>();
        //编辑预编译语句
        StringBuilder findAll_sql =new StringBuilder("SELECT * FROM logistics") ;

        //获取logistics中的元组总数
        int totalNum = CommonDao.getInstance().count(conn, "logistics");

        PreparedStatement findAll_pstmt = conn.prepareStatement(findAll_sql.toString());
        //执行查询并获取查询结果
        ResultSet findAll_resultSet = findAll_pstmt.executeQuery();
        //赋值
        while (findAll_resultSet.next()){
            Integer id = findAll_resultSet.getInt("id");
            String no = findAll_resultSet.getString("no");
            String name = findAll_resultSet.getString("name");
            Integer phoneNumber = findAll_resultSet.getInt("phoneNumber");
            String description = findAll_resultSet.getString("description");
            //创建相应的顾客对象
            Logistics logistics = new Logistics(id,no,name,phoneNumber,description);
            //将查询出的单个顾客添加进集合中
            logisticses.add(logistics);
        }
        //释放资源
        JdbcHelper.close(findAll_resultSet,findAll_pstmt);
        //返回查询结果
        return logisticses;
    }
    /**
     * @param logistics 要增加到数据库表中的Logistics对象
     * @param conn 来自Service的Connection对象
     * @return 成功增加true，失败：false
     * @throws SQLException
     */
    //增加单个顾客方法
    public boolean add(Logistics logistics,Connection conn) throws SQLException{
        //编辑预编译语句
        PreparedStatement add_pstmt = conn.prepareStatement("INSERT INTO logistics " +
                " (no,name,phoneNumber,description)" +
                " VALUES (?,?,?,?)");
        //设置预编译参数
        add_pstmt.setString(1,logistics.getNo());
        add_pstmt.setString(2,logistics.getName());
        add_pstmt.setInt(3,logistics.getPhoneNumber());
        add_pstmt.setString(4,logistics.getDescription());
        //获取执行结果
        int affectedRow = add_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(add_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
    /**
     * @param conn  logistics 要更新到数据库表中的Logistics对象
     * @param conn 来自Service的Connection对象
     * @return update成功返回true，反之，false
     * @throws SQLException
     */
    //更新顾客信息方法
    public boolean update(Logistics logistics,Connection conn) throws SQLException{
        //编辑预编译语句
        PreparedStatement update_pstmt = conn.prepareStatement("UPDATE logistics SET " +
                " no=?,name=?,phoneNumber=?,description=? WHERE id=?");
        //设置预编译参数
        update_pstmt.setInt(5,logistics.getId());
        update_pstmt.setString(1,logistics.getNo());
        update_pstmt.setString(2,logistics.getName());
        update_pstmt.setInt(3,logistics.getPhoneNumber());
        update_pstmt.setString(4,logistics.getDescription());
        //获取执行结果
        int affectedRow = update_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(update_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
    /**
     * @param conn  logistics 要删除数据库表中的Logistics对象
     * @param conn 来自Service的Connection对象
     * @return 删除成功返回true，反之，false
     * @throws SQLException
     */
    //删除顾客方法
    public boolean delete(Integer id,Connection conn)throws SQLException{
        //设置预编译语句
        PreparedStatement delete_pstmt = conn.prepareStatement("DELETE FROM logistics WHERE id=?");
        //设置预编译参数
        delete_pstmt.setInt(1,id);
        //获取执行结果
        int affectedRow = delete_pstmt.executeUpdate();
        //释放资源
        JdbcHelper.close(delete_pstmt);
        //返回执行结果
        return affectedRow>0;
    }
}
